﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace QuanLyKhachHang.Data
{
    class TTKhachHang
    {
        public string MaKhachHang, TenCongTy, LinhVuc, DiaChi, DienThoai, DanhGia, GhiChu, MaNhomKhachHang;
        public DateTime NgayLienHe;
        public TTKhachHang() { }
        public TTKhachHang(string MaKhachHang, string TenCongTy, string LinhVuc, string DiaChi, string DienThoai, DateTime NgayLienHe, string DanhGia, string GhiChu, string MaNhomKhachHang)
        {
            this.MaKhachHang = MaKhachHang;
            this.TenCongTy = TenCongTy;
            this.LinhVuc = LinhVuc;
            this.DiaChi = DiaChi;
            this.DienThoai = DienThoai;
            this.NgayLienHe = NgayLienHe;
            this.DanhGia = DanhGia;
            this.GhiChu = GhiChu;
            this.MaNhomKhachHang = MaNhomKhachHang;
        }
    }

    class NVKhachHang
    {
        public static DataTable ChonTatCa()
        {
            return Connect.cnSQL.ExecQuery("Select KhachHang.*, NhomKhachHang.TenNhomKhachHang FROM  KhachHang LEFT OUTER JOIN  NhomKhachHang ON KhachHang.MaNhomKhachHang = NhomKhachHang.MaNhomKhachHang");
        }
        public static DataTable ChonTatCa(string MaKhachHang)
        {
            return Connect.cnSQL.ExecQuery("Select KhachHang.*, NhomKhachHang.TenNhomKhachHang from KhachHang,NhomKhachHang where NhomKhachHang.MaNhomKhachHang=KhachHang.MaNhomKhachHang and MaKhachHang='" + MaKhachHang.Trim() + "'");
        }
        public static bool LoiDienThoai(string SoDienThoai)
        {
            SoDienThoai = SoDienThoai.Replace("+84", " ").Trim();
            string SoDien = (SoDienThoai.StartsWith("0")?SoDienThoai.Substring(1,SoDienThoai.Length-1):SoDienThoai);
            if (Connect.cnSQL.ExecQuery("Select Khachhang.MaKhachHang  from KhachHang where DienThoai like '%" + SoDien + "%'").Rows.Count > 0)
            {
                // Da ton tai
                return false;
            }
            else
            {
                // Chua ton tai
                return true;
            }
        }

        public static bool LoiCongTy(string TenCongTy)
        {
            if (string.IsNullOrEmpty(TenCongTy)==true)
            {
                return false;
            }
            if (Connect.cnSQL.ExecQuery("Select Khachhang.MaKhachHang  from KhachHang " + Connect.ScriptSQL.LikeSearch(TenCongTy, "TenCongTy")).Rows.Count > 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
           
        }

        public static bool Them(TTKhachHang KhachHang)
        {
            Connect.cnSQL.ExecNonQuery("Insert into KhachHang values('" + KhachHang.MaKhachHang.Trim() + "',N'" + KhachHang.TenCongTy.Trim() + "',N'" + KhachHang.LinhVuc.Trim() + "',N'" + KhachHang.DiaChi.Trim() + "','" + KhachHang.DienThoai.Trim() + "',convert(varchar(11), '" + KhachHang.NgayLienHe.ToString("yyyy-MM-dd hh:mm:ss") + "', 103),N'" + KhachHang.DanhGia.Trim() + "',N'" + KhachHang.GhiChu.Trim() + "','" + KhachHang.MaNhomKhachHang.Trim() + "')");
            return (!Connect.Tools.CoLoi);
        }

        public static bool Sua(TTKhachHang KhachHang)
        {
            Connect.cnSQL.ExecNonQuery("Update KhachHang Set TenCongTy=N'" + KhachHang.TenCongTy + "', LinhVuc=N'" + KhachHang.LinhVuc + "', DiaChi=N'" + KhachHang.DiaChi + "',DienThoai='" + KhachHang.DienThoai + "', NgayLienHe=convert(varchar(11), '" + KhachHang.NgayLienHe.ToString("yyyy-MM-dd hh:mm:ss") + "', 103)" + ", DanhGia=N'" + KhachHang.DanhGia + "', GhiChu=N'" + KhachHang.GhiChu + "' , MaNhomKhachHang='" + KhachHang.MaNhomKhachHang + "'  where MaKhachHang=N'" + KhachHang.MaKhachHang + "'");
            return (!Connect.Tools.CoLoi);
        }


        public static bool Xoa(string Ma)
        {
            Connect.cnSQL.ExecNonQuery("Delete KhachHang where MaKhachHang ='" + Ma + "'");
            return (!Connect.Tools.CoLoi);
        }

        public static bool LoiDiaChi(string DiaChi)
        {
            if (string.IsNullOrEmpty(DiaChi) == true)
            {
                return false;
            }
            if (Connect.cnSQL.ExecQuery("Select Khachhang.MaKhachHang from KhachHang " + Connect.ScriptSQL.LikeSearch(DiaChi, "DiaChi")).Rows.Count > 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
    }
}
